package by.bsu.rfe.belianko.euniversity.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

import by.bsu.rfe.belianko.euniversity.constants.ErrorMessages;
import by.bsu.rfe.belianko.euniversity.constants.LoggerMessages;
import by.bsu.rfe.belianko.euniversity.constants.SQLQueries;
import by.bsu.rfe.belianko.euniversity.exceptions.ConnectionExceptions;
import by.bsu.rfe.belianko.euniversity.exceptions.DaoTechnicalException;
import by.bsu.rfe.belianko.euniversity.model.Faculty;
import by.bsu.rfe.belianko.euniversity.pool.ConnectionPool;

/**
 * Load faculties from DB using pool of JDBC connections.
 * 
 * @author Vitali_Belianko
 * 
 */

public class FacultiesDAO implements IFacultiesDAO {
	private static final String CREATED_FACULTY_LIST = "Created faculty list";
	private static final String DESCRIPTION = "description";
	private static final String SUBMITTED = "submitted";
	private static final String PLAN = "plan";
	private static final String FACULTY_NAME = "faculty_name";
	private static final String FACULTY_ID = "faculty_id";
	private static final Logger LOGGER = Logger.getLogger(FacultiesDAO.class);
	private static ConnectionPool connectionPool;
	private static FacultiesDAO instance;

	public static FacultiesDAO getInstance() {
		if (instance == null) {
			instance = new FacultiesDAO();
		}
		return instance;
	}

	private FacultiesDAO() {

	}

	/**
	 * Creates list of faculty from DB.
	 * 
	 * @return list of faculty
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public List<Faculty> getFacultyList() throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);
		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement statement = null;
		List<Faculty> facultyList = new ArrayList<Faculty>();
		Faculty faculty = null;
		ResultSet resultSet = null;
		try {
			statement = connection.prepareStatement(SQLQueries.GET_FACULTY_LIST);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			try {
				resultSet = statement.executeQuery();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

				while (resultSet.next()) {
					faculty = buildFaculty(resultSet);
					facultyList.add(faculty);

				}

				LOGGER.debug(CREATED_FACULTY_LIST);

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);
		} finally {
			if (resultSet != null)
				try {
					resultSet.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_RESULT_SET, e);
				}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

		return facultyList;
	}

	/**
	 * Returns faculty from DB by id.
	 * 
	 * @param facultyId
	 * 
	 * @return faculty with such id
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public Faculty getFacultyById(long facultyId) throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);
		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Faculty faculty = null;
		try {
			statement = connection.prepareStatement(SQLQueries.GET_FACULTY_BY_ID);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			statement.setLong(1, facultyId);
			try {
				resultSet = statement.executeQuery();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

				while (resultSet.next()) {
					faculty = buildFaculty(resultSet);
				}

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);
		} finally {
			if (resultSet != null)
				try {
					resultSet.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_RESULT_SET, e);
				}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

		return faculty;
	}

	/**
	 * Builds faculty from DB.
	 * 
	 * @param resultSet
	 * 
	 * @return faculty
	 * 
	 * @throws DaoTechnicalException
	 */
	private Faculty buildFaculty(ResultSet resultSet) throws DaoTechnicalException {
		Faculty faculty = null;
		try {
			long facultyId = resultSet.getLong(FACULTY_ID);
			String facultyName = resultSet.getString(FACULTY_NAME);
			int plan = resultSet.getInt(PLAN);
			int submitted = resultSet.getInt(SUBMITTED);
			String description = resultSet.getString(DESCRIPTION);
			faculty = new Faculty();
			faculty.setFacultyId(facultyId);
			faculty.setFacultyName(facultyName);
			faculty.setPlan(plan);
			faculty.setSubmitted(submitted);
			faculty.setDescription(description);
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CELL_DATA, e);
		}
		return faculty;
	}
}
